from django.shortcuts import render
from django.db.models import *
from typing import List, Union
from django.db import connection
from django.db import connections
from datetime import datetime
from .models import Down, Repair, Parse, Down1, Parse1, DownParse, ParseDown
import json

# 起始页
def stoppage1(request):
    start_time, stop_time = initial_time()

    # 树图的数据部分格式为json
    cmy1, cmy2 = zfh(start_time, stop_time)
    time = [start_time, stop_time]
    # 数据透视表初始化
    love = init(start_time, stop_time)
    context = {'time': time, 'cmy1': json.dumps(cmy1), 'cmy2': json.dumps(cmy2), 'love': love}
    print(love)

    return render(request, 'production_ms/stoppage/template-stoppage.html', context)


# 树图更新
def stoppage2(request):
    start_time = request.POST['start']
    stop_time = request.POST['stop']
    cmy1, cmy2 = zfh(start_time, stop_time)
    context = {'cmy1': json.dumps(cmy1), 'cmy2': json.dumps(cmy2)}
    return render(request, 'production_ms/stoppage/stoppage-tree.html', context)


# 数据透视表
def stoppage4(request):
    crew = request.POST['a']
    procedure = request.POST['b']
    device = request.POST['c']
    fault = request.POST['d']
    start_time = request.POST['e']
    stop_time = request.POST['f']
    cmy_r = f"date >= '{start_time}' and date <= '{stop_time}' and fault1 <> '换型'"
    jsk = []
    if crew != '':
        jsk.append(f"unit = '{crew}'")
    if procedure != '':
        jsk.append(f"fault1 = '{procedure}'")
    if device != '':
        jsk.append(f"fault2 = '{device}'")
    if fault != '':
        jsk.append(f"fault3 = '{fault}'")
    jsk_r = ""
    for i in jsk:
        jsk_r = jsk_r + i + ' and '
    jsk_r = jsk_r + cmy_r

    # 返回时间序列
    time = get_time(start_time, stop_time)

    # 根据配置好的条件查询数据
    xph = zzy(jsk_r)
    love = [time, xph]
    context = {'love': love}

    return render(request, 'production_ms/stoppage/stoppage-bar.html', context)


# 弹出的列表页
def stoppage3(request):
    start_time = request.GET.get("a")
    stop_time = request.GET.get("b")
    crew = request.GET.get("c")
    procedure = request.GET.get("d")
    device = request.GET.get("e")
    fault = request.GET.get("f")
    cmy_r = f"date >= '{start_time}' and date <= '{stop_time}' and fault1 <> '换型'"
    jsk = []
    if crew != '':
        jsk.append(f"unit = '{crew}'")
    if procedure != '':
        jsk.append(f"fault1 = '{procedure}'")
    if device != '':
        jsk.append(f"fault2 = '{device}'")
    if fault != '':
        jsk.append(f"fault3 = '{fault}'")
    jsk_r = ""
    for i in jsk:
        jsk_r = jsk_r + i + ' and '
    jsk_r = jsk_r + cmy_r
    with connections['production'].cursor() as cur:
        cur.execute(f'''
            select to_char(date, 'YYYY-MM-DD'),unit,fault1,fault2,fault3,start_time,stop_time,duration,describe,result,section 
            from production_ms_repair 
            where {jsk_r}
        ''')
        datalist = cur.fetchall()
    context = {'datalist': datalist}

    return render(request, 'production_ms/stoppage/stoppage_data.html', context)


# 数据透视表初始化
def init(start_time, stop_time):
    cmy_r = f"date >= '{start_time}' and date <= '{stop_time}' and fault1 <> '换型'"

    cmy1 = get_time(start_time, stop_time)
    cmy2 = f0(cmy_r)

    return [cmy1, cmy2]


def dict_factory(zfh, hxy):
    for i in hxy:
        zfh.append({"name": i[0], "value": str(i[1]) + '-' + str(i[2]), "children": [], 'collapsed': False})
    return zfh


# 规定哪些机组展开哪些机组不展开
def dict_factory1(zfh, hxy):
    for j in hxy:
        zfh.append({"name": j[0], "value": str(j[1]) + '-' + str(j[2]), "children": []})
        break
    for i in hxy[1:]:
        zfh.append({"name": i[0], "value": str(i[1]) + '-' + str(i[2]), "children": [], 'collapsed': True})
    return zfh


def zfh(start_time, stop_time):
    hxy_r = f"date >= '{start_time}' and date <= '{stop_time}' and fault1 <> '换型'"
    # con = sqlite3.connect(DATABASE_PATH)
    # cur = con.cursor()

    # 输入sql语句，返回数组 如果查询结果为空就返回[]
    # 你以为要处理可能返回查询结果为0的问题，笑死，根本不用解决，直接返回空数组。for中的变量尽量不要相同
    with connections['production'].cursor() as cur:

        cur.execute(f'''SELECT cast(sum(duration) as int) FROM production_ms_repair where {hxy_r}''')
        total = list(map(lambda i: i[0], cur.fetchall()))
        # total = cur.fetchall()
        cur.execute(f'''SELECT count(*) FROM production_ms_repair where {hxy_r}''')
        number = list(map(lambda i: i[0], cur.fetchall()))
        # number = cur.fetchall()
        # print(type(total[0]), type(number[0]))
        cmy1 = [{"name": '全部机组', "value": str(total[0]) + '-' + str(number[0]), "children": []}]

        # 先调查有几个机组 ['502', '601', '761', '114', '127', '165', '219', '273', '762', '764']
        cur.execute(f'''
            SELECT unit, cast(sum(duration) as int),count(*)
            FROM production_ms_repair
            where {hxy_r} 
            group by unit 
            order by cast(sum(duration) as int) desc 
        ''')
        crew_total = cur.fetchall()
        # 在这里将所有机组都装入对象，只有第一个机组在展示时需要展开，所以使用特制函数dict_factory1
        cmy1[0]['children'] = dict_factory1(cmy1[0]['children'], crew_total)
        # 结果 [{'name': '127', 'value': 0.477, 'children': []}......]

        # 逐个机组轮询 在这个循环中机组名称为crew
        for crew in cmy1[0]['children']:
            cur.execute(f'''
                select fault1,cast(sum(duration) as int),count(*)
                from production_ms_repair 
                where {hxy_r} and unit = '{crew['name']}'
                group by fault1 
                order by cast(sum(duration) as int) desc  
            ''')
            procedure_total = cur.fetchall()
            crew['children'] = dict_factory(crew['children'], procedure_total)
            for device in crew['children']:
                cur.execute(f'''
                    select fault2, cast(sum(duration) as int),count(*)
                    from production.public.production_ms_repair 
                    where {hxy_r} and unit = '{crew['name']}' and fault1 = '{device['name']}' 
                    group by fault2 
                    order by cast(sum(duration) as int) desc 
                ''')
                device_total = cur.fetchall()
                device['children'] = dict_factory(device['children'], device_total)
                for failure in device['children']:
                    cur.execute(f'''
                        select fault3, cast(sum(duration) as int),count(*)
                        from production.public.production_ms_repair
                        where {hxy_r} and unit = '{crew['name']}' and fault1 = '{device['name']}' and fault2 = '{failure['name']}' 
                        group by fault3 
                        order by cast(sum(duration) as int) desc  
                    ''')
                    failure_total = cur.fetchall()
                    failure['children'] = dict_factory(failure['children'], failure_total)

        # 改进型
        cmy2 = {"name": '全部故障', "value": 0, "children": []}
        cur.execute(f'''SELECT cast(sum(duration) as int) FROM production.public.production_ms_repair where {hxy_r}''')
        total = list(map(lambda i: i[0], cur.fetchall()))
        cur.execute(f'''SELECT count(*) FROM production.public.production_ms_repair where {hxy_r}''')
        number = list(map(lambda i: i[0], cur.fetchall()))
        cmy2['value'] = str(total[0]) + '-' + str(number[0])
        cur.execute(f'''SELECT fault1, cast(sum(duration) as int),count(*) 
            from production.public.production_ms_repair 
            where {hxy_r} 
            GROUP BY fault1
            order by cast(sum(duration) as int) desc
        ''')
        gongxu = cur.fetchall()
        dict_factory1(cmy2['children'], gongxu)
        # print(cmy2)
        for gong_xu in cmy2["children"]:
            # print(gong_xu)
            cur.execute(f'''
                SELECT fault2, cast(sum(duration) as int),count(*)
                from production.public.production_ms_repair 
                where {hxy_r} and fault1 = '{gong_xu['name']}' 
                GROUP BY fault2 
                order by cast(sum(duration) as int) desc 
            ''')
            shebei = cur.fetchall()
            gong_xu['children'] = dict_factory(gong_xu['children'], shebei)
            for she_bei in gong_xu['children']:
                cur.execute(f'''
                    SELECT fault3, cast(sum(duration) as int),count(*)
                    from production.public.production_ms_repair 
                    where {hxy_r} and fault1 = '{gong_xu['name']}' and fault2 = '{she_bei['name']}' 
                    GROUP BY fault3 
                    order by cast(sum(duration) as int) desc 
                ''')
                guzhangbuwei = cur.fetchall()
                # print('-------------')
                # print(she_bei['children'])
                she_bei['children'] = dict_factory(she_bei['children'], guzhangbuwei)

    # cur.close()
    # con.close()

    return cmy1, cmy2


# 使用此代码用于检查
# select *
# from repair
# where 生产线 = '219' and 日期 >= '2021-07-01' and 日期 <= '2021-07-30' and 工序 = '下料' and 设备 = '打包' and 故障部位 = '电磁阀'

# 函数区域
def get_time(start_time, stop_time):
    with connections['production'].cursor() as cur:
        cur.execute(f'''
            select distinct to_char(date, 'YYYY-MM-DD') 
            from production_ms_repair
            where date >= '{start_time}' and date <= '{stop_time}'
            order by to_char(date, 'YYYY-MM-DD')
        ''')
        cmy = list(map(lambda i: i[0], cur.fetchall()))
    return cmy


def zzy(jsk_r):
    with connections['production'].cursor() as cur:
        cur.execute(f'''
            select to_char(date, 'YYYY-MM-DD'), cast(sum(duration) as int)
            from production_ms_repair 
            where {jsk_r}
            GROUP BY date''')
        cmy = list(map(lambda i: i[0], cur.fetchall()))
    return cmy


# 仅用于页面初始化
def f0(cmy_r):
    with connections['production'].cursor() as cur:
        cur.execute(f'''
            select to_char(date, 'YYYY-MM-DD'), cast(sum(duration) as int)
            from production_ms_repair
            where {cmy_r}
            GROUP BY date''')
        # cmy = list(map(lambda i: i[0], cur.fetchall()))
        cmy = cur.fetchall()
    return cmy


# 获取数据库中最新的数据的日期作为开始和结束日期
# 返回未选择日期时的初始时间
def initial_time():
    a = Parse.objects.values_list('date').latest('date')[0].strftime('%Y-%m-%d')
    return a, a
